package laborum2011.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import laborum2011.excepcion.DAOExcepcion;
import laborum2011.modelo.*;
import laborum2011.util.ConexionBD;
import laborum2011.dao.BaseDAO;

public class LaboralDAO extends BaseDAO{

	public void insertar(Laboral po) throws DAOExcepcion {
		System.out.println("LaboralDAO: insertar(detalleestudios vo)");
		String query = "INSERT INTO laboral(id_postulante,empresa,cargo,descripcion,referencia,fecini, fecfin,tele,correo,estado) " +
				       "VALUES (?,?,?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, po.getId_postulante());
			stmt.setString(2, po.getEmpresa());
			stmt.setString(3, po.getCargo());
			stmt.setString(4, po.getDescripcion());
			stmt.setString(5, po.getReferencia());
			stmt.setTimestamp(6, po.getFecini());
			stmt.setTimestamp(7, po.getFecfin());
			stmt.setString(8, po.getTele());
			stmt.setString(9, po.getCorreo());
			stmt.setInt(10, po.getEstado());
			int i = stmt.executeUpdate();

			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public Collection<Laboral> listar() throws DAOExcepcion {
		System.out.println("LaboralDAO: listar()");
		Collection<Laboral> c = new ArrayList<Laboral>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT id_postulante,empresa,cargo,descripcion,referencia,fecini, fecfin,tele,correo,estado " +
					"FROM laboral where estado=1";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Laboral vo = new Laboral();
				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setEmpresa(rs.getString("empresa"));
				vo.setCargo(rs.getString("cargo"));
				vo.setDescripcion(rs.getString("descripcion"));
				vo.setReferencia(rs.getString("referencia"));
				vo.setFecini(rs.getTimestamp("fecini"));
				vo.setFecfin(rs.getTimestamp("fecfin"));
				vo.setTele(rs.getString("tele"));
				vo.setCorreo(rs.getString("correo"));
				vo.setEstado(rs.getInt("estado"));
				c.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}	

	public Collection<Laboral> listarXIdpost(int id) throws DAOExcepcion {
		System.out.println("DetEstDAO: listar()");
		Collection<Laboral> c = new ArrayList<Laboral>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
		
			String query = "SELECT id_postulante,empresa,cargo,descripcion,referencia,fecini, fecfin,tele,correo,estado " +
			"FROM laboral where id_postulante=? and estado=1";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Laboral vo = new Laboral();
				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setEmpresa(rs.getString("empresa"));
				vo.setCargo(rs.getString("cargo"));
				vo.setDescripcion(rs.getString("descripcion"));
				vo.setReferencia(rs.getString("referencia"));
				vo.setFecini(rs.getTimestamp("fecini"));
				vo.setFecfin(rs.getTimestamp("fecfin"));
				vo.setTele(rs.getString("tele"));
				vo.setCorreo(rs.getString("correo"));
				vo.setEstado(rs.getInt("estado"));
				c.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}	

	public Laboral listarXId(int id) throws DAOExcepcion {
		System.out.println("DetEstDAO: listar()");

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Laboral vo = new Laboral();
		try {
		
			String query = "SELECT id_postulante,empresa,cargo,descripcion,referencia,fecini, fecfin,tele,correo,estado " +
			"FROM laboral where id_laboral=? and estado=1";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				
				vo.setId_postulante(rs.getInt("id_postulante"));
				vo.setEmpresa(rs.getString("empresa"));
				vo.setCargo(rs.getString("cargo"));
				vo.setDescripcion(rs.getString("descripcion"));
				vo.setReferencia(rs.getString("referencia"));
				vo.setFecini(rs.getTimestamp("fecini"));
				vo.setFecfin(rs.getTimestamp("fecfin"));
				vo.setTele(rs.getString("tele"));
				vo.setCorreo(rs.getString("correo"));
				vo.setEstado(rs.getInt("estado"));
			
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}	
	
	public void actualizar(Laboral po) throws DAOExcepcion {
		System.out.println("LaboralDAO: actualizar(Laboral vo) "+ po.getId_laboral());
		String query = "update laboral set " 
						+"id_postulante	 = ?," 
						+"empresa = ?," 
						+"cargo = ?," 
						+"descripcion = ?," 
						+"referencia = ?," 
						+"fecini  = ?," 
						+"fecfin   = ?," 
						+"tele   = ?," 
						+"correo   = ?" 
						+"where idlaboral =?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, po.getId_postulante());
			stmt.setString(2, po.getEmpresa());
			stmt.setString(3, po.getCargo());
			stmt.setString(4, po.getDescripcion());
			stmt.setString(5, po.getReferencia());
			stmt.setTimestamp(6, po.getFecini());
			stmt.setTimestamp(7, po.getFecfin());
			stmt.setString(8, po.getTele());
			stmt.setString(9, po.getCorreo());
			stmt.setInt(10, po.getId_laboral());
			int i = stmt.executeUpdate();

			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public void eliminar(int id) throws DAOExcepcion {
		System.out.println("DetEstDAO: eliminar(Int id)");
		String query = "update laboral set estado = 0 where id_postulante = ?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, id);

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
}
